﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;

using HK2009.Common;
using HK2009.BusinessFacade;
using HK2009.BusinessFacadeHandler;
using HK2009.EntityQuery;
using System.Data.SqlClient;
using System.Text;
using System.Collections.Generic;
using System.Threading;
using Microsoft.Reporting.WebForms;
using System.IO;

public partial class Reports_LclTruckingDellveryReport : BasePage
{


    IRolePurviewHandler userManage = new RolePurviewHandler();
    ISyClientHandler clientManage = new SyClientHandler();
    ISyMarketHandler markManage = new SyMarketHandler();
    ISyPlaceHandler placeManage = new SyPlaceHandler();
    ISyDestinationHandler destManage = new SyDestinationHandler();
    IBNBookingNoteHandler bookingNoteManage = new BNBookingNoteHandler();
    DataBase db = new DataBase();
    ISystemHandler systemManage = new SystemHandler();
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            ISystemHandler sysManage = new SystemHandler();
            string strPermission = sysManage.GetLoginUserPermissions();
            if (!(strPermission.Contains("1085")))
            {
                Response.Redirect("~/NotPurview.aspx");
                return;
            }

            FillDDl();

            ViewState["Value2"] = DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd") + " 00:00:00";
            ViewState["Name2"] = "dtl.DeliveryDate";
            ViewState["Value3"] = DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd") + " 23:59:59";
            PreviewReport();
        }
    }

    #region FillDDl
    private void FillDDl()
    {
        //绑定托运人
        DataTable dt = clientManage.GetClientListByTypeID(int.Parse(Enums.CT.TYR.ToString("d")));
        lstShipper.DataSource = dt;

        if (Thread.CurrentThread.CurrentUICulture.ToString().ToLower().Equals("en"))
        {
            lstShipper.DataTextField = "ENGShortName";
        }
        else if (Thread.CurrentThread.CurrentUICulture.ToString().ToLower().Equals("zh-hk"))
        {
            lstShipper.DataTextField = "HKShortName";
        }
        else
        {
            lstShipper.DataTextField = "CNShortName";
        }
        lstShipper.DataValueField = "PKID";
        lstShipper.DataBind();

        //工厂
        dt = clientManage.GetClientListByTypeID(int.Parse(Enums.CT.GC.ToString("d")));
        for (int i = 0; i < dt.Rows.Count; i++)
        {
            ListItem item = new ListItem();
            if (Thread.CurrentThread.CurrentUICulture.ToString().ToLower().Equals("zh-hk"))
            {
                // ddlVendor.DataTextField = "HKShortName";
                item.Text = dt.Rows[i]["ENGShortName"].ToString() + "/" + dt.Rows[i]["HKShortName"].ToString();
            }
            else
            {
                item.Text = dt.Rows[i]["ENGShortName"].ToString() + "/" + dt.Rows[i]["CNShortName"].ToString();
                //ddlVendor.DataTextField = "CNShortName";
            }
            item.Value = dt.Rows[i]["PKID"].ToString();
            // ddlVendor.DataValueField = "PKID";
            lstVendor.Items.Add(item);
        }

        //仓库
        dt = clientManage.GetClientListByTypeID(int.Parse(Enums.CT.CK.ToString("d")));
        lstWhse.DataSource = dt;
        if (Thread.CurrentThread.CurrentUICulture.ToString().ToLower().Equals("en"))
        {
            lstWhse.DataTextField = "ENGShortName";
        }
        else if (Thread.CurrentThread.CurrentUICulture.ToString().ToLower().Equals("zh-hk"))
        {
            lstWhse.DataTextField = "HKShortName";
        }
        else
        {
            lstWhse.DataTextField = "CNShortName";
        }
        lstWhse.DataValueField = "PKID";
        lstWhse.DataBind();

        ////客户客人
        //dt = clientManage.GetClientListByTypeID(int.Parse(Enums.CT.KHKR.ToString("d")));
        //lstCustomer.DataSource = dt;
        //if (Thread.CurrentThread.CurrentUICulture.ToString().ToLower().Equals("en"))
        //{
        //    lstCustomer.DataTextField = "ENGShortName";
        //}
        //else if (Thread.CurrentThread.CurrentUICulture.ToString().ToLower().Equals("zh-hk"))
        //{
        //    lstCustomer.DataTextField = "HKShortName";
        //}
        //else
        //{
        //    lstCustomer.DataTextField = "CNShortName";
        //}
        //lstCustomer.DataValueField = "PKID";
        //lstCustomer.DataBind();
    }
    #endregion

    //有交货日期有收货日期
    #region GetDataTable 获取主报表的数据源
    private DataTable GetDataTable()
    {
        string sqlStr = string.Empty;
        if (systemManage.IsCompanyRoles2())//公司用户2
        {
            sqlStr = @"SELECT distinct dtl.Appointament,linkMan.LinkMan as UserID,
                          market.CNName as BusinessType,
                          shipper.ENGShortName as BusinessUnit,
                        bn.ShipperID,
                        dtl.WhouseID,
                        dtl.VendorID,
                        bn.SourceCode,
                        bn.CPO,customer.ENGShortName as CustomerID,
                        dtl.PcNO,bn.InvoiceNO as InvoiceNO,dtl.SONO,
                        vendor.MVendorCode,
                        vendor.ENGShortName as Vendor,
                        dtl.ItemNO, bn.WinClose,
                        dtl.JobDate,dtl.DeliveryDate,dtl.PCS,dtl.CTN,
                         dtl.GrossWT,dtl.VOL, 
                        place.HKDistrict as Area,
                        place.HKName as Place,
                        whse.ENGShortName as Whs,
                        truck.LicenseNO as CarNO,
                        model.Model as TruckSize,
                        customer.MVendorCode as CustomerVendorCode,
                        dtl.ConfirmNO,	   
	                        dtl.Remark as Remarks
                        FROM BN_BookingNote bn INNER JOIN
	                        BN_BookingNoteDtl dtl ON bn.PKID = dtl.BookingNoteID LEFT JOIN
	                        Sy_ClientLinkMan linkMan ON bn.ShipperLMID = linkMan.PKID LEFT JOIN
	                        Sy_Client shipper ON bn.ShipperID = shipper.PKID LEFT JOIN
	                        Sy_Client vendor ON dtl.VendorID = vendor.PKID LEFT JOIN
	                        Sy_Client whse ON dtl.WhouseID = whse.PKID LEFT JOIN
	                        Sy_Client customer ON bn.CustomerID = customer.PKID LEFT JOIN
	                        Sy_Market market on bn.MarketID =market.PKID LEFT JOIN
	                        Sy_Place place on vendor.PlaceID=place.PKID LEFT JOIN 
                        	
	                        BN_BookingDtl bookingDtl on bookingDtl.BookingNoteDtlID=dtl.PKID  JOIN
	                        BN_ShippingOrderDtl orderdtl on orderdtl.BookingDtlID= bookingDtl.PKID  join
	                        BN_SendTruck sendTruck on sendTruck.OrderID=orderdtl.OrderID  join
	                        BN_SendTruckDtl sendTruckdtl on sendTruckdtl.SendTruckID=SendTruck.PKID  JOIN	
	                        BN_Truck truck on sendTruckdtl.TruckID=truck.PKID  join
                        	
	                        Sy_ContainerModel model on sendTruckdtl.TypeID=model.PKID

WHERE bn.Status = 1
	
	AND dtl.JobDate is not null and dtl.DeliveryDate is not null 
    AND dtl.FCLLCL='LCL' 
  AND (shipper.ENGShortName='MAT' or shipper.ENGShortName='FP' or shipper.ENGName='MAT' or shipper.ENGName='FP')
and bn.CreatedUser in(
select UserID from Users where  ClientID in(
select ClientID from Users where UserID=@UserID))
";
        }
        else
        {
            sqlStr = @"SELECT distinct dtl.Appointament,linkMan.LinkMan as UserID,
                          market.CNName as BusinessType,
                          shipper.ENGShortName as BusinessUnit,
                        bn.ShipperID,
                        dtl.WhouseID,
                        dtl.VendorID,
                        bn.SourceCode,
                        bn.CPO,customer.ENGShortName as CustomerID,
                        dtl.PcNO,bn.InvoiceNO as InvoiceNO,dtl.SONO,
                        vendor.MVendorCode,
                        vendor.ENGShortName as Vendor,
                        dtl.ItemNO, bn.WinClose,
                        dtl.JobDate,dtl.DeliveryDate,dtl.PCS,dtl.CTN,
                         dtl.GrossWT,dtl.VOL, 
                        place.HKDistrict as Area,
                        place.HKName as Place,
                        whse.ENGShortName as Whs,
                        truck.LicenseNO as CarNO,
                        model.Model as TruckSize,
                        customer.MVendorCode as CustomerVendorCode,
                        dtl.ConfirmNO,	   
	                        dtl.Remark as Remarks
                        FROM BN_BookingNote bn INNER JOIN
	                        BN_BookingNoteDtl dtl ON bn.PKID = dtl.BookingNoteID LEFT JOIN
	                        Sy_ClientLinkMan linkMan ON bn.ShipperLMID = linkMan.PKID LEFT JOIN
	                        Sy_Client shipper ON bn.ShipperID = shipper.PKID LEFT JOIN
	                        Sy_Client vendor ON dtl.VendorID = vendor.PKID LEFT JOIN
	                        Sy_Client whse ON dtl.WhouseID = whse.PKID LEFT JOIN
	                        Sy_Client customer ON bn.CustomerID = customer.PKID LEFT JOIN
	                        Sy_Market market on bn.MarketID =market.PKID LEFT JOIN
	                        Sy_Place place on vendor.PlaceID=place.PKID LEFT JOIN 
                        	
	                        BN_BookingDtl bookingDtl on bookingDtl.BookingNoteDtlID=dtl.PKID  JOIN
	                        BN_ShippingOrderDtl orderdtl on orderdtl.BookingDtlID= bookingDtl.PKID  join
	                        BN_SendTruck sendTruck on sendTruck.OrderID=orderdtl.OrderID  join
	                        BN_SendTruckDtl sendTruckdtl on sendTruckdtl.SendTruckID=SendTruck.PKID  JOIN	
	                        BN_Truck truck on sendTruckdtl.TruckID=truck.PKID  join
                        	
	                        Sy_ContainerModel model on sendTruckdtl.TypeID=model.PKID

WHERE bn.Status = 1
	
	AND dtl.JobDate is not null and dtl.DeliveryDate is not null 
    AND dtl.FCLLCL='LCL' 
  AND (shipper.ENGShortName='MAT' or shipper.ENGShortName='FP' or shipper.ENGName='MAT' or shipper.ENGName='FP')
";
        }

        SqlParameter[] parms = null;
        string clause = string.Empty;
        ProcessParms(ref clause, ref parms);
        sqlStr += clause;

        return db.RunSqlTab(sqlStr, parms);
    }
    #endregion

    #region Preview Report
    private void PreviewReport()
    {
        rptViewer.LocalReport.ReportPath = "Reports/rptLclTruckingDellvery.rdlc";

        rptViewer.LocalReport.DataSources.Clear();
        DataTable dt = GetDataTable();
        if (dt != null && dt.Rows.Count > 0)
        {
            rptViewer.Visible = true;
            lblNoRecord.Visible = false;
            ReportDataSource datasource = new ReportDataSource("dsLclTruckingDellvery_DataTable1", dt);
            rptViewer.LocalReport.DataSources.Add(datasource);

           ReportParameter[] parameters = new ReportParameter[1];
           parameters[0] = new ReportParameter("TotalNum", dt.Rows.Count.ToString());
            //parameters[1] = new ReportParameter("DateTo", txtDateTo.Text);
            //parameters[2] = new ReportParameter("DateTimeString", ddlDateString.SelectedItem.Text);
            rptViewer.LocalReport.SetParameters(parameters);
            rptViewer.LocalReport.Refresh();
        }
        else
        {
            rptViewer.Visible = false;
            lblNoRecord.Visible = true;
        }
    }
    #endregion



    /// <summary>
    /// 处理传入参数。。返回SQL条件和sqlparameter
    /// </summary>
    /// <param name="sqlstr"></param>
    /// <param name="parms"></param>
    /// <summary>
    /// 处理传入参数。。返回SQL条件和sqlparameter
    /// </summary>
    /// <param name="sqlstr"></param>
    /// <param name="parms"></param>
    private void ProcessParms(ref string sqlstr, ref SqlParameter[] parms)
    {
        List<SqlParameter> list = new List<SqlParameter>();
        sqlstr = "";

        if (ViewState["Value2"] != null && ViewState["Value3"] != null)
        {
            list.Add(new SqlParameter("@DateFrom", ViewState["Value2"].ToString().ToString()));
            list.Add(new SqlParameter("@DateTo", ViewState["Value3"].ToString()));
            sqlstr += " and " + ViewState["Name2"].ToString() + " between @DateFrom and @DateTo ";
        }
        else if (ViewState["Value2"] != null && ViewState["Value3"] == null)
        {
            list.Add(new SqlParameter("@DateFrom", ViewState["Value2"].ToString().ToString()));
            sqlstr += " and " + ViewState["Name2"].ToString() + " >= @DateFrom ";
        }
        else if (ViewState["Value2"] == null && ViewState["Value3"] != null)
        {
            list.Add(new SqlParameter("@DateFrom", ViewState["Value3"].ToString().ToString()));
            sqlstr += " and " + ViewState["Name2"].ToString() + " <= @DateFrom ";
        }

        if (ViewState["Shipper"] != null)
        {
            // lst.Add(new SqlParameter("@Shipper", ViewState["Shipper"].ToString()));
            sqlstr += " and bn.ShipperID in (" + ViewState["Shipper"].ToString() + ")";
        }

        if (ViewState["Whse"] != null)
        {
            sqlstr += " and dtl.WhouseID in(" + ViewState["Whse"].ToString() + ")";
        }
        if (ViewState["Vendor"] != null)
        {
            // lst.Add(new SqlParameter("@Shipper", ViewState["Shipper"].ToString()));
            sqlstr += " and dtl.VendorID in (" + ViewState["Vendor"].ToString() + ")";
        }

        if (systemManage.IsCompanyRoles2())//公司用户2
        {
            list.Add(new SqlParameter("@UserID", Session["UserID"].ToString()));

        }

        parms = list.ToArray();
    }


    protected void btnSearch_Click(object sender, EventArgs e)
    {
        if (txtDateFrom.Text != string.Empty && txtDateTo.Text != string.Empty)
        {
            ViewState["Value2"] = txtDateFrom.Text.Trim() + " 00:00:00";
            ViewState["Name2"] = ddlDateString.SelectedValue;
            ViewState["Value3"] = txtDateTo.Text.Trim() + " 23:59:59";
        }
        else if (txtDateFrom.Text != string.Empty && txtDateTo.Text == string.Empty)
        {
            ViewState["Value2"] = txtDateFrom.Text.Trim() + " 00:00:00";
            ViewState["Name2"] = ddlDateString.SelectedValue;
            ViewState["Value3"] = null;
        }
        else if (txtDateFrom.Text == string.Empty && txtDateTo.Text != string.Empty)
        {
            ViewState["Value2"] = null;
            ViewState["Name2"] = ddlDateString.SelectedValue;
            ViewState["Value3"] = txtDateTo.Text.Trim() + " 00:00:00";
        }
        else
        {
            ViewState["Value2"] = DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd") + " 00:00:00";
            ViewState["Name2"] ="dtl.DeliveryDate";
            ViewState["Value3"] = DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd") + " 23:59:59";
        }



        if (lstSelShipper.Items.Count > 0)
        {
            string item = string.Empty;
            for (int i = 0; i < lstSelShipper.Items.Count; i++)
            {
                item += lstSelShipper.Items[i].Value + ",";
            }
            ViewState["Shipper"] = item.Substring(0, item.Length - 1);
        }
        else
        {
            ViewState["Shipper"] = null;
        }

        if (lstSelVendor.Items.Count > 0)
        {
            string item = string.Empty;
            for (int i = 0; i < lstSelVendor.Items.Count; i++)
            {
                item += lstSelVendor.Items[i].Value + ",";
            }
            ViewState["Vendor"] = item.Substring(0, item.Length - 1);
        }
        else
        {
            ViewState["Vendor"] = null;
        }

        if (lstSelWhse.Items.Count > 0)
        {
            string item = string.Empty;
            for (int i = 0; i < lstSelWhse.Items.Count; i++)
            {
                item += lstSelWhse.Items[i].Value + ",";
            }
            ViewState["Whse"] = item.Substring(0, item.Length - 1);
        }
        else
        {
            ViewState["Whse"] = null;
        }

        PreviewReport();
    }

    protected void btnEmail_Click(object sender, EventArgs e)
    {
        if (this.rptViewer.LocalReport.DataSources.Count > 0)
        {
            Microsoft.Reporting.WebForms.Warning[] Warnings;
            string[] strStreamIds;
            string strMimeType;
            string strEncoding;
            string strFileNameExtension;

            byte[] bytes = this.rptViewer.LocalReport.Render("Excel", null, out strMimeType, out strEncoding, out strFileNameExtension, out strStreamIds, out Warnings);

            // string strFilePath = @"D:\report.xls";
            string filename = FileTools.AutoFileName() + ".xls";
            string strFilePath = "FclTruckingDeliveryReport_" + filename;
            using (System.IO.FileStream fs = new FileStream(Server.MapPath("~").ToString() + "/EmailReport/" + strFilePath, FileMode.Create))
            {
                fs.Write(bytes, 0, bytes.Length);
            }
        }
    }

    protected void btnAdd_Click(object sender, EventArgs e)
    {
        if (lstShipper.SelectedValue != string.Empty)
        {
            for (int i = 0; i < lstShipper.Items.Count; i++)
            {
                bool issel = false;
                if (lstShipper.Items[i].Selected)
                {
                    for (int j = 0; j < lstSelShipper.Items.Count; j++)
                    {
                        if (lstShipper.Items[i].Value == lstSelShipper.Items[j].Value)
                        {
                            issel = true;
                            break;
                        }
                    }

                    if (!issel)
                    {
                        ListItem item = new ListItem();
                        item.Text = lstShipper.Items[i].Text;
                        item.Value = lstShipper.Items[i].Value;
                        lstSelShipper.Items.Add(item);
                    }
                }
            }
        }
        else
        {

        }
    }

    protected void btnDelete_Click(object sender, EventArgs e)
    {
        if (lstSelShipper.Items.Count > 0)
        {
            ListItemCollection lstColl = new ListItemCollection();
            for (int i = 0; i < lstSelShipper.Items.Count; i++)
            {
                if (!lstSelShipper.Items[i].Selected)
                {
                    ListItem item = new ListItem(lstSelShipper.Items[i].Text, lstSelShipper.Items[i].Value);
                    lstColl.Add(item);
                }
            }
            if (lstColl != null && lstColl.Count > 0)
            {
                lstSelShipper.Items.Clear();
                for (int j = 0; j < lstColl.Count; j++)
                {
                    lstSelShipper.Items.Add(lstColl[j]);
                }
            }
            else
            {
                lstSelShipper.Items.Clear();
            }

        }
    }

    //protected void btnCustomerAdd_Click(object sender, EventArgs e)
    //{
    //    if (lstCustomer.SelectedValue != string.Empty)
    //    {
    //        for (int i = 0; i < lstCustomer.Items.Count; i++)
    //        {
    //            bool issel = false;
    //            if (lstCustomer.Items[i].Selected)
    //            {
    //                for (int j = 0; j < lstSelCustomer.Items.Count; j++)
    //                {
    //                    if (lstCustomer.Items[i].Value == lstSelCustomer.Items[j].Value)
    //                    {
    //                        issel = true;
    //                        break;
    //                    }
    //                }

    //                if (!issel)
    //                {
    //                    ListItem item = new ListItem();
    //                    item.Text = lstCustomer.Items[i].Text;
    //                    item.Value = lstCustomer.Items[i].Value;
    //                    lstSelCustomer.Items.Add(item);
    //                }
    //            }
    //        }
    //    }
    //    else
    //    {

    //    }
    //}

    //protected void btnCustomerDel_Click(object sender, EventArgs e)
    //{
    //    if (lstSelCustomer.Items.Count > 0)
    //    {
    //        ListItemCollection lstColl = new ListItemCollection();
    //        for (int i = 0; i < lstSelCustomer.Items.Count; i++)
    //        {
    //            if (!lstSelCustomer.Items[i].Selected)
    //            {
    //                ListItem item = new ListItem(lstSelCustomer.Items[i].Text, lstSelCustomer.Items[i].Value);
    //                lstColl.Add(item);
    //            }
    //        }
    //        if (lstColl != null && lstColl.Count > 0)
    //        {
    //            lstSelCustomer.Items.Clear();
    //            for (int j = 0; j < lstColl.Count; j++)
    //            {
    //                lstSelCustomer.Items.Add(lstColl[j]);
    //            }
    //        }
    //        else
    //        {
    //            lstSelCustomer.Items.Clear();
    //        }

    //    }
    //}

    protected void btnWhseAdd_Click(object sender, EventArgs e)
    {
        if (lstWhse.SelectedValue != string.Empty)
        {
            for (int i = 0; i < lstWhse.Items.Count; i++)
            {
                bool issel = false;
                if (lstWhse.Items[i].Selected)
                {
                    for (int j = 0; j < lstSelWhse.Items.Count; j++)
                    {
                        if (lstWhse.Items[i].Value == lstSelWhse.Items[j].Value)
                        {
                            issel = true;
                            break;
                        }
                    }

                    if (!issel)
                    {
                        ListItem item = new ListItem();
                        item.Text = lstWhse.Items[i].Text;
                        item.Value = lstWhse.Items[i].Value;
                        lstSelWhse.Items.Add(item);
                    }
                }
            }
        }
        else
        {

        }
    }

    protected void btnWhseDel_Click(object sender, EventArgs e)
    {
        if (lstSelWhse.Items.Count > 0)
        {
            ListItemCollection lstColl = new ListItemCollection();
            for (int i = 0; i < lstSelWhse.Items.Count; i++)
            {
                if (!lstSelWhse.Items[i].Selected)
                {
                    ListItem item = new ListItem(lstSelWhse.Items[i].Text, lstSelWhse.Items[i].Value);
                    lstColl.Add(item);
                }
            }
            if (lstColl != null && lstColl.Count > 0)
            {
                lstSelWhse.Items.Clear();
                for (int j = 0; j < lstColl.Count; j++)
                {
                    lstSelWhse.Items.Add(lstColl[j]);
                }
            }
            else
            {
                lstSelWhse.Items.Clear();
            }

        }
    }

    protected void btnVendorAdd_Click(object sender, EventArgs e)
    {
        if (lstVendor.SelectedValue != string.Empty)
        {
            for (int i = 0; i < lstVendor.Items.Count; i++)
            {
                bool issel = false;
                if (lstVendor.Items[i].Selected)
                {
                    for (int j = 0; j < lstSelVendor.Items.Count; j++)
                    {
                        if (lstVendor.Items[i].Value == lstSelVendor.Items[j].Value)
                        {
                            issel = true;
                            break;
                        }
                    }

                    if (!issel)
                    {
                        ListItem item = new ListItem();
                        item.Text = lstVendor.Items[i].Text;
                        item.Value = lstVendor.Items[i].Value;
                        lstSelVendor.Items.Add(item);
                    }
                }
            }
        }
        else
        {

        }
    }

    protected void btnVendorDel_Click(object sender, EventArgs e)
    {
        if (lstSelVendor.Items.Count > 0)
        {
            ListItemCollection lstColl = new ListItemCollection();
            for (int i = 0; i < lstSelVendor.Items.Count; i++)
            {
                if (!lstSelVendor.Items[i].Selected)
                {
                    ListItem item = new ListItem(lstSelVendor.Items[i].Text, lstSelVendor.Items[i].Value);
                    lstColl.Add(item);
                }
            }
            if (lstColl != null && lstColl.Count > 0)
            {
                lstSelVendor.Items.Clear();
                for (int j = 0; j < lstColl.Count; j++)
                {
                    lstSelVendor.Items.Add(lstColl[j]);
                }
            }
            else
            {
                lstSelVendor.Items.Clear();
            }

        }
    }
}
